--    Program: 		CAPAREXT_SYSTEM_OPTION.sql
--    Description:	Extract AR System Option information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   l_set_of_books_id		number;
   
   cursor cur_system_option (p_set_of_books_id number) is
      select * from AR_SYSTEM_PARAMETERS_ALL
	  where set_of_books_id = p_set_of_books_id;

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_SYSTEM_OPTION.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books short name not found');
       --raise;
   end;      

   for cur in cur_system_option(l_set_of_books_id) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file, 
	   cur.set_of_books_id|| chr(9) ||
	   cur.created_by|| chr(9) ||
	   cur.creation_date|| chr(9) ||
	   cur.last_updated_by|| chr(9) ||
	   cur.last_update_date|| chr(9) ||
	   cur.last_update_login|| chr(9) ||
	   cur.accounting_method|| chr(9) ||
	   cur.accrue_interest|| chr(9) ||
	   cur.UNEARNED_DISCOUNT|| chr(9) ||
	   cur.partial_discount_flag|| chr(9) ||
	   cur.print_remit_to|| chr(9) ||
	   cur.default_cb_due_date|| chr(9) ||
	   cur.auto_site_numbering|| chr(9) ||
	   cur.CASH_BASIS_SET_OF_BOOKS_ID|| chr(9) ||
	   cur.code_combination_id_gain|| chr(9) ||
	   cur.autocash_hierarchy_id|| chr(9) ||
	   cur.run_gl_journal_import_flag|| chr(9) ||
	   cur.cer_split_amount|| chr(9) ||
	   cur.cer_dso_days|| chr(9) ||
	   cur.posting_days_per_cycle|| chr(9) ||
	   cur.address_validation|| chr(9) ||
	   cur.attribute1|| chr(9) ||
	   cur.attribute2|| chr(9) ||
	   cur.attribute_category|| chr(9) ||
	   cur.attribute3|| chr(9) ||
	   cur.attribute4|| chr(9) ||
	   cur.attribute5|| chr(9) ||
	   cur.attribute6|| chr(9) ||
	   cur.attribute7|| chr(9) ||
	   cur.attribute8|| chr(9) ||
	   cur.attribute9|| chr(9) ||
	   cur.attribute10|| chr(9) ||
	   cur.calc_discount_on_lines_flag|| chr(9) ||
	   cur.change_printed_invoice_flag|| chr(9) ||
	   cur.code_combination_id_loss|| chr(9) ||
	   cur.create_reciprocal_flag|| chr(9) ||
	   cur.default_country|| chr(9) ||
	   cur.default_territory|| chr(9) ||
	   cur.generate_customer_number|| chr(9) ||
	   cur.invoice_deletion_flag|| chr(9) ||
	   cur.location_structure_id|| chr(9) ||
	   cur.site_required_flag|| chr(9) ||
	   cur.tax_allow_compound_flag|| chr(9) ||
	   cur.tax_invoice_print|| chr(9) ||
	   cur.tax_method|| chr(9) ||
	   cur.tax_use_customer_exempt_flag|| chr(9) ||
	   cur.tax_use_cust_exc_rate_flag|| chr(9) ||
	   cur.tax_use_loc_exc_rate_flag|| chr(9) ||
	   cur.tax_use_product_exempt_flag|| chr(9) ||
	   cur.TAX_USE_PROD_EXC_RATE_FLAG|| chr(9) ||
	   cur.TAX_USE_SITE_EXC_RATE_FLAG|| chr(9) ||
	   cur.AI_LOG_FILE_MESSAGE_LEVEL|| chr(9) ||
	   cur.AI_MAX_MEMORY_IN_BYTES|| chr(9) ||
	   cur.AI_ACCT_FLEX_KEY_LEFT_PROMPT|| chr(9) ||
	   cur.AI_MTL_ITEMS_KEY_LEFT_PROMPT|| chr(9) ||
	   cur.AI_TERRITORY_KEY_LEFT_PROMPT|| chr(9) ||
	   cur.AI_PURGE_INTERFACE_TABLES_FLAG|| chr(9) ||
	   cur.AI_ACTIVATE_SQL_TRACE_FLAG|| chr(9) ||
	   cur.DEFAULT_GROUPING_RULE_ID|| chr(9) ||
	   cur.SALESREP_REQUIRED_FLAG|| chr(9) ||
	   cur.ATTRIBUTE11|| chr(9) ||
	   cur.ATTRIBUTE12|| chr(9) ||
	   cur.ATTRIBUTE13|| chr(9) ||
	   cur.ATTRIBUTE14|| chr(9) ||
	   cur.ATTRIBUTE15|| chr(9) ||
	   cur.AUTO_REC_INVOICES_PER_COMMIT|| chr(9) ||
	   cur.AUTO_REC_RECEIPTS_PER_COMMIT|| chr(9) ||
	   cur.PAY_UNRELATED_INVOICES_FLAG|| chr(9) ||
	   cur.PRINT_HOME_COUNTRY_FLAG|| chr(9) ||
	   cur.LOCATION_TAX_ACCOUNT|| chr(9) ||
	   cur.FROM_POSTAL_CODE|| chr(9) ||
	   cur.TO_POSTAL_CODE|| chr(9) ||
	   cur.TAX_REGISTRATION_NUMBER|| chr(9) ||
	   cur.POPULATE_GL_SEGMENTS_FLAG|| chr(9) ||
	   cur.UNALLOCATED_REVENUE_CCID|| chr(9) ||
	   cur.ORG_ID|| chr(9) ||
	   cur.TAX_CURRENCY_CODE|| chr(9) ||
	   cur.TAX_PRECISION|| chr(9) ||
	   cur.TAX_MINIMUM_ACCOUNTABLE_UNIT|| chr(9) ||
	   cur.TAX_HEADER_LEVEL_FLAG|| chr(9) ||
	   cur.TAX_ROUNDING_RULE|| chr(9) ||
	   cur.TAX_ROUNDING_ALLOW_OVERRIDE|| chr(9) ||
	   cur.TAX_CODE|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE1|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE2|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE3|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE4|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE5|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE6|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE7|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE8|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE9|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE10|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE11|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE12|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE13|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE14|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE15|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE16|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE17|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE18|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE19|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE20|| chr(9) ||
	   cur.GLOBAL_ATTRIBUTE_CATEGORY|| chr(9) ||
	   cur.RULE_SET_ID|| chr(9) ||
	   cur.TAX_USE_ACCOUNT_EXC_RATE_FLAG|| chr(9) ||
	   cur.TAX_USE_SYSTEM_EXC_RATE_FLAG|| chr(9) ||
	   cur.TAX_HIER_SITE_EXC_RATE|| chr(9) ||
	   cur.TAX_HIER_CUST_EXC_RATE|| chr(9) ||
	   cur.TAX_HIER_PROD_EXC_RATE|| chr(9) ||
	   cur.TAX_HIER_ACCOUNT_EXC_RATE|| chr(9) ||
	   cur.TAX_HIER_SYSTEM_EXC_RATE|| chr(9) ||
	   cur.TAX_ENFORCE_ACCOUNT_FLAG|| chr(9) ||
	   cur.TAX_DATABASE_VIEW_SET|| chr(9) ||
	   cur.INCLUSIVE_TAX_USED|| chr(9) ||
	   cur.CODE_COMBINATION_ID_ROUND|| chr(9) ||
	   cur.TRX_HEADER_LEVEL_ROUNDING|| chr(9) ||
	   cur.TRX_HEADER_ROUND_CCID|| chr(9) ||
	   cur.FINCHRG_RECEIVABLES_TRX_ID|| chr(9) ||
	   cur.SALES_TAX_GEOCODE|| chr(9) ||
	   cur.BILLS_RECEIVABLE_ENABLED_FLAG|| chr(9) ||
	   cur.TA_INSTALLED_FLAG|| chr(9) ||
	   cur.REV_TRANSFER_CLEAR_CCID|| chr(9) ||
	   cur.SALES_CREDIT_PCT_LIMIT|| chr(9) ||
	   cur.MAX_WRTOFF_AMOUNT|| chr(9) ||
	   cur.IREC_CC_RECEIPT_METHOD_ID|| chr(9) ||
	   cur.SHOW_BILLING_NUMBER_FLAG|| chr(9) ||
	   cur.CROSS_CURRENCY_RATE_TYPE|| chr(9) ||
	   cur.DOCUMENT_SEQ_GEN_LEVEL|| chr(9) ||
	   cur.CALC_TAX_ON_CREDIT_MEMO_FLAG|| chr(9) ||
	   cur.IREC_BA_RECEIPT_METHOD_ID|| chr(9) ||
	   cur.PAYMENT_THRESHOLD|| chr(9) ||
	   cur.STANDARD_REFUND|| chr(9) ||
	   cur.CREDIT_CLASSIFICATION1|| chr(9) ||
	   cur.CREDIT_CLASSIFICATION3|| chr(9) ||
	   cur.CREDIT_CLASSIFICATION2
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': System Option not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_SYSTEM_OPTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
